from django.http import HttpResponse,HttpResponseRedirect
from django.template import loader
from django.urls import reverse
from django.db.models import Q
# Create your views here.
import psycopg2
import random



# 返回对应表中的所有行信息
def search_all(table_name):
    conn=psycopg2.connect(database='mmsdb',user='js',password='thInkin59g@',
                      host='172.23.16.1',port='5566')
    # 创建cursor对象：
    cursor = conn.cursor()
    # 使用cursor对象来执行SQL语句。
    cursor.execute("select * from \"js-mode\"."+table_name)
    res=[]

    tmp=cursor.fetchall()
    conn.commit()


    cursor.execute("select column_name from information_schema.columns  \
                    where table_schema='js-mode' and table_name='"+table_name+"';")
    tmp_idx=cursor.fetchall()
    conn.commit()
    conn.close()

    for i in range(len(tmp)):
        res.append({})
        for j in range(len(tmp[i])):
            res[i][tmp_idx[j][0]]=tmp[i][j]

    return res

# 添加新的用户
def add_to_user(id,name,passwd,sex):
    t=search_all('users')
    for var in t:
        if var['user_id']==str(id):
            return False
    
    conn=psycopg2.connect(database='mmsdb',user='js',password='thInkin59g@',
                      host='172.23.16.1',port='5566')
    cursor = conn.cursor() 
    cursor.execute("INSERT INTO \"js-mode\".users (user_id,user_name,passwd,user_sex) VALUES ('"+ id +"', '"+ name +"', '"+passwd+"', '"+sex+"');")
    conn.commit()
    conn.close()

#根据前端给的对应email的信息（实际即对应视图行信息），分别创建其基表（email、appendix）的行信息
def add_to_email_and_appendix(sender_id,receiver_id, email_id, email_theme, email_content,appendix_id,appendix_url,appendix_name,sent_time):
    tmp_emails = search_all('emails')
    tmp_appendixs = search_all('appendixs')
    for var in tmp_emails:
        if var['sender_id'] == str(id):
            return False


    conn = psycopg2.connect(database='mmsdb', user='js', password='thInkin59g@',
                            host='172.23.16.1', port='5566')
    cursor = conn.cursor()
    # 创建appendix的对应新数据
    cursor.execute(
        "INSERT INTO \"js-mode\".appendixs (appendix_id,appendix_name,appendix_url) VALUES ('" + appendix_id + "', '" + appendix_name + "', '" + appendix_url + "');")

    conn.commit()
    # 创建email的对应新数据
    cursor.execute(
        "INSERT INTO \"js-mode\".emails (email_id,email_theme,email_content,sent_time,sender_id,receiver_id,appendix_id) VALUES ('" + str(email_id) + "', '" + email_theme + "', '" + email_content + "', '" + sent_time + "', '" + str(sender_id) + "', '" + receiver_id + "', '" + str(appendix_id) + "');")

    conn.commit()

    conn.close()
    return True

#根据前端给的对应draft的信息（实际即对应视图行信息），分别创建其基表（draft、appendix）的行信息
def add_to_draft_and_appendix(sender_id,receiver_id, draft_id, draft_theme, draft_content,appendix_id,appendix_url,appendix_name,last_edit_time):
    tmp_drafts = search_all('drafts')
    tmp_appendixs = search_all('appendixs')
    for var in tmp_drafts:
        if var['sender_id'] == str(id):
            return False


    conn = psycopg2.connect(database='mmsdb', user='js', password='thInkin59g@',
                            host='172.23.16.1', port='5566')
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO \"js-mode\".appendixs (appendix_id,appendix_name,appendix_url) VALUES ('" + appendix_id + "', '" + appendix_name + "', '" + appendix_url + "');")

    conn.commit()

    cursor.execute(
        "INSERT INTO \"js-mode\".drafts (draft_id,draft_theme,draft_content,last_edit_time,sender_id,receiver_id,appendix_id) VALUES ('" + str(draft_id) + "', '" + draft_theme + "', '" + draft_content + "', '" + last_edit_time + "', '" + str(sender_id) + "', '" + receiver_id + "', '" + str(appendix_id) + "');")

    conn.commit()

    conn.close()
    return True

#添加新的记事本记录
def add_to_notebook(owner_id,notebook_id,book_theme,book_content,edit_time):
    tmp_notebooks = search_all('notebooks')
    for var in tmp_notebooks:
        if var['owner_id'] == str(id):
            return False


    conn = psycopg2.connect(database='mmsdb', user='js', password='thInkin59g@',
                            host='172.23.16.1', port='5566')
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO \"js-mode\".notebooks (owner_id,notebook_id,book_theme,book_content,edit_time) VALUES ('" + str(owner_id) + "', '" + str(notebook_id) + "', '" + book_theme + "', '" + book_content + "', '" + edit_time + "');")

    conn.commit()

    conn.close()
    return True

# 添加新的通讯录记录
def add_to_contact(owner_id,contact_id,remark):
    tmp_contacts = search_all('contacts')


    conn = psycopg2.connect(database='mmsdb', user='js', password='thInkin59g@',
                            host='172.23.16.1', port='5566')
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO \"js-mode\".contacts (owner_id,contact_id,remark) VALUES ('" + str(owner_id) + "', '" + str(contact_id) + "', '" + remark + "');")

    conn.commit()

    conn.close()
    return True

# 删除特定用户
def delete_record(id):
    conn=psycopg2.connect(database='mmsdb',user='js',password='thInkin59g@',
                      host='172.23.16.1',port='5566')
    cursor = conn.cursor() 
    cursor.execute("DELETE FROM \"js-mode\".users where user_id='"+ str(id) +"'")
    conn.commit()
    conn.close()

# 删除某个草稿的SQL操作
def delete_draft_process(id):
    conn=psycopg2.connect(database='mmsdb',user='js',password='thInkin59g@',
                      host='172.23.16.1',port='5566')
    cursor = conn.cursor()
    cursor.execute("select appendix_id FROM \"js-mode\".drafts where draft_id='" + str(id) + "'")
    tmp = cursor.fetchall()
    conn.commit()
    cursor.execute("DELETE FROM \"js-mode\".drafts where draft_id='"+ str(id) +"'")
    conn.commit()
    cursor.execute("DELETE FROM \"js-mode\".appendixs where appendix_id='" + str(tmp[0][0]) + "'")
    conn.commit()
    conn.close()

# 删除某个通讯录记录的SQL操作
def delete_contact_process(contact_id):
    conn=psycopg2.connect(database='mmsdb',user='js',password='thInkin59g@',
                      host='172.23.16.1',port='5566')
    cursor = conn.cursor()
    cursor.execute("DELETE FROM \"js-mode\".contacts where contact_id='"+ str(contact_id) +"'")
    conn.commit()
    conn.close()

# 发送草稿的SQL操作
def send_draft_process(appendix_id, email_id, receiver_id, email_theme, email_content, sender_id, sent_time, appendix_name,appendix_url):
    tmp_emails = search_all('emails')
    tmp_appendixs = search_all('appendixs')
    for var in tmp_emails:
        if var['sender_id'] == str(id):
            return False

    conn = psycopg2.connect(database='mmsdb', user='js', password='thInkin59g@',
                            host='172.23.16.1', port='5566')
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO \"js-mode\".appendixs (appendix_id,appendix_name,appendix_url) VALUES ('" + str(
            500-appendix_id) + "', '" + appendix_name + "', '" + appendix_url + "');"
    )
    conn.commit()
    cursor.execute(
        "INSERT INTO \"js-mode\".emails (email_id,email_theme,email_content,sent_time,sender_id,receiver_id,appendix_id) VALUES ('" + str(
            email_id) + "', '" + email_theme + "', '" + email_content + "', '" + sent_time + "', '" + str(
            sender_id) + "', '" + str(receiver_id) + "', '" + str(500-appendix_id) + "');")

    conn.commit()

    conn.close()
    return True

# 更新某个用户信息记录的SQL操作
def update_record(x,y,z,u):

    
    conn=psycopg2.connect(database='mmsdb',user='js',password='thInkin59g@',
                      host='172.23.16.1',port='5566')
    cursor = conn.cursor() 
    
    cursor.execute("update \"js-mode\".users set passwd='"+y+"',user_name='"+z+"',user_sex='"+u+"' where user_id='"+ str(x) +"'")
    
    conn.commit()
    conn.close()
    return True

# 获取并当前登入的用户ID
def index(request):


    conn = psycopg2.connect(database='mmsdb', user='js', password='thInkin59g@',
                            host='172.23.16.1', port='5566')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM \"js-mode\".cur_user")

    tmp = cursor.fetchall()
    conn.commit()

    conn.close()

    template=loader.get_template('index.html')
    context={'cur_user':tmp[0][1],}

    return HttpResponse(template.render(context,request))

# 创建登入页面，，从数据库获取信息并传入前端页面
def login(request):

    template=loader.get_template('login.html')

    context={'test':'111',}
    return HttpResponse(template.render(context,request))

#创建check（检测登入账号是否正确、是否为管理员）页面，，从数据库获取信息并传入前端页面
def check(request):
    user=search_all("users")
    x=request.POST['user_id']
    y=request.POST['passwd']
    flag=0
    statu=0
    for var in user:
        if var['user_id']==x and var['passwd']==y:
            flag=1
            break

    #判断当前用户是否为管理员
    if x == '123456':
        statu=1
    else :
        statu=0

    # 用户名密码正确且为用户
    if flag==1 and statu==0:
        conn = psycopg2.connect(database='mmsdb', user='js', password='thInkin59g@',
                                host='172.23.16.1', port='5566')
        cursor = conn.cursor()
        cursor.execute(
            "update \"js-mode\".cur_user set cur_user_id='" + x + "' where flag=1""")
        conn.commit()
        conn.close()

        return HttpResponseRedirect(reverse('index'))

    # 用户名密码正确且为管理员
    elif flag==1 and statu==1:
        return HttpResponseRedirect(reverse('adminpage'))
    else:
        return HttpResponseRedirect(reverse('login'))

# 创建新增用户页面，，从数据库获取信息并传入前端页面，在该页面转向 实际执行新增的函数addrecord
def add(request):
    template=loader.get_template('add.html')
    return HttpResponse(template.render({},request))

# 新增用户的执行操作
def addrecord(request):
    idd=request.POST['user_id']
    name=request.POST['user_name']
    passwd=request.POST['passwd']
    sex = request.POST['user_sex']

    flag=add_to_user(idd,name,passwd,sex)
    if flag==False:
        return HttpResponseRedirect(reverse('faultpage'))
    return HttpResponseRedirect(reverse('adminpage'))

# 创建删除用户页面，，从数据库获取信息并传入前端页面
def delete(request,id):
    delete_record(id)

    return HttpResponseRedirect(reverse('adminpage'))

# 创建删除草稿的页面，，从数据库获取信息并传入前端页面，在该页面转向 实际执行删除的函数delete_draft_process
def delete_draft(request,id):
    conn = psycopg2.connect(database='mmsdb', user='js', password='thInkin59g@',
                            host='172.23.16.1', port='5566')
    cursor = conn.cursor()
    cursor.execute("select sender_id FROM \"js-mode\".d_app where draft_id='" + str(id) + "'")
    tmp = cursor.fetchall()
    conn.commit()

    delete_draft_process(id)

    return HttpResponseRedirect("http://localhost:8000/members/draft/"+str(tmp[0][0]))


def send_draft(request,id):
    conn = psycopg2.connect(database='mmsdb', user='js', password='thInkin59g@',
                            host='172.23.16.1', port='5566')
    cursor = conn.cursor()
    cursor.execute("select sender_id FROM \"js-mode\".d_app where draft_id='" + str(id) + "'")
    tmp = cursor.fetchall()
    conn.commit()
    cursor.execute("select * FROM \"js-mode\".d_app where draft_id='" + str(id) + "'")
    tmp_all = cursor.fetchall()
    conn.commit()

    appendix_id, draft_id, receiver_id, draft_theme, draft_content, sender_id, sent_time, appendix_name,appendix_url=\
        tmp_all[0][0], tmp_all[0][1], tmp_all[0][2],tmp_all[0][3], tmp_all[0][4], tmp_all[0][5], tmp_all[0][6], tmp_all[0][7], tmp_all[0][8]
    send_draft_process(appendix_id, draft_id, receiver_id, draft_theme, draft_content, sender_id, sent_time, appendix_name,appendix_url)

    delete_draft(request, id)
    return HttpResponseRedirect("http://localhost:8000/members/draft/"+str(tmp[0][0]))




def delete_contact(request,id):
    conn = psycopg2.connect(database='mmsdb', user='js', password='thInkin59g@',
                            host='172.23.16.1', port='5566')
    cursor = conn.cursor()
    cursor.execute("select cur_user_id FROM \"js-mode\".cur_user where flag=1")
    tmp = cursor.fetchall()
    conn.commit()

    delete_contact_process(id)

    return HttpResponseRedirect("http://localhost:8000/members/contact/"+str(tmp[0][0]))

# 创建更新通讯录的页面，，从数据库获取信息并传入前端页面，在该页面转向 实际执行删除的函数update_contact
def update_c(request,id):

    context = {'contact': id, }
    template = loader.get_template('update_contact.html')
    return HttpResponse(template.render(context, request))

def update_contact(request,id):
    x = request.POST['remark']
    conn = psycopg2.connect(database='mmsdb', user='js', password='thInkin59g@',
                            host='172.23.16.1', port='5566')
    cursor = conn.cursor()
    cursor.execute("select cur_user_id FROM \"js-mode\".cur_user where flag=1")
    tmp = cursor.fetchall()
    conn.commit()

    cursor.execute("update \"js-mode\".contacts set remark='" + str(x) + "'where contact_id='" + str(id) + "'")
    conn.commit()

    return HttpResponseRedirect("http://localhost:8000/members/contact/"+str(tmp[0][0]))


# 创建更新用户信息的页面，，从数据库获取信息并传入前端页面，在该页面转向 实际执行更新的函数 updaterecord
def update(request,id):
    users=search_all('users')
    update_user={}
    for var in users:
        if var['user_id']==str(id):
            update_user=var
    context={'users':update_user,}
    template=loader.get_template('update.html')
    return HttpResponse(template.render(context,request))


def updaterecord(request,id):
    x=request.POST['user_id']
    y=request.POST['passwd']
    z=request.POST['user_name']
    u= request.POST['user_sex']
    
    flag=update_record(x,y,z,u)

    if flag==False:
        return HttpResponseRedirect(reverse('faultpage'))

    return HttpResponseRedirect(reverse('adminpage'))

# 创建用户信息页面，从数据库获取信息并传入前端页面
def infopage(request,id):
    # 获取该用户信息
    users={}
    tmp=search_all("users")
    for var in tmp:
        if var['user_id']==str(id):
            users=var
            break


    template=loader.get_template('infopage.html')
    
    context={'users':users,}
    return HttpResponse(template.render(context,request))


# 创建收件箱页面，从数据库获取信息并传入前端页面
def inbox(request, id):
    # 获取该邮件信息
    emails_appendixs = []
    tmp_email = search_all("e_app")
    for var in tmp_email:
        if var['receiver_id'] == id:
            emails_appendixs.append(var)

    template = loader.get_template('inbox.html')

    context = {'email_appendix': emails_appendixs,}
    return HttpResponse(template.render(context, request))

# 创建写邮件的页面，，从数据库获取信息并传入前端页面，在该页面转向 实际执行写邮件的函数 write_email
def write(request,id):
    users = search_all('users')
    update_user = {}
    for var in users:
        if var['user_id'] == str(id):
            update_user = var
    context = {'users': update_user, }
    template = loader.get_template('write.html')
    return HttpResponse(template.render(context, request))

# 写邮件，获取前端页面输入的信息，并存入数据库
def write_email(request, id):
    receiver_id = request.POST["receiver_id"]
    email_id = request.POST['email_id']
    email_theme = request.POST['email_theme']
    email_content = request.POST['email_content']
    appendix_url = request.POST['appendix_url']
    appendix_name = request.POST['appendix_name']
    appendix_id = request.POST['appendix_id']
    sent_time = request.POST['sent_time']
    flag = add_to_email_and_appendix(id,receiver_id,email_id,email_theme,email_content,appendix_id,appendix_url,appendix_name,sent_time)
    if flag == False:
        return HttpResponseRedirect(reverse('faultpage'))
    return HttpResponseRedirect(reverse('index'))

# 保存为草稿，获取前端页面输入的信息，并存入数据库
def save_as_draft(request, id):
    receiver_id = request.POST["receiver_id"]
    draft_id = request.POST['email_id']
    draft_theme = request.POST['email_theme']
    draft_content = request.POST['email_content']
    appendix_url = request.POST['appendix_url']
    appendix_name = request.POST['appendix_name']
    appendix_id = request.POST['appendix_id']
    last_edit_time = request.POST['sent_time']
    flag = add_to_draft_and_appendix(id,receiver_id,draft_id,draft_theme,draft_content,appendix_id,appendix_url,appendix_name,last_edit_time)
    if flag == False:
        return HttpResponseRedirect(reverse('faultpage'))
    return HttpResponseRedirect(reverse('index'))

# 创建 新增记事本 的页面，从数据库获取信息并传入前端页面，在该页面转向 实际执行写邮件的函数 write_note
def note(request,id):
    users = search_all('users')
    update_user = {}
    for var in users:
        if var['user_id'] == str(id):
            update_user = var
    context = {'users': update_user, }
    template = loader.get_template('note.html')
    return HttpResponse(template.render(context, request))


def write_note(request, id):
    notebook_id = request.POST["notebook_id"]
    book_theme = request.POST['book_theme']
    book_content = request.POST['book_content']
    edit_time = request.POST['edit_time']

    flag = add_to_notebook(id,notebook_id,book_theme,book_content,edit_time)
    if flag == False:
        return HttpResponseRedirect(reverse('faultpage'))
    return HttpResponseRedirect(reverse('index'))

# 创建 记事本 的页面，从数据库获取信息并传入前端页面
def notebook(request, id):
    # 获取该邮件信息
    notebook = []
    tmp_notebook = search_all("notebooks")
    for var in tmp_notebook:
        if var['owner_id'] == id:
            notebook.append(var)

    template = loader.get_template('notebook.html')

    context = {'notebooks': notebook,}
    return HttpResponse(template.render(context, request))

# 创建 新增通讯录 的页面，从数据库获取信息并传入前端页面，在该页面转向 实际执行写邮件的函数 write_contact
def my_contact(request,id):
    users = search_all('users')
    update_user = {}
    for var in users:
        if var['user_id'] == str(id):
            update_user = var
    context = {'users': update_user, }
    template = loader.get_template('my_contact.html')
    return HttpResponse(template.render(context, request))

def write_contact(request, id):
    contact_id = request.POST["contact_id"]
    remark = request.POST['remark']

    flag = add_to_contact(id,contact_id,remark)
    if flag == False:
        return HttpResponseRedirect(reverse('faultpage'))
    return HttpResponseRedirect("http://localhost:8000/members/contact/"+str(id))

# 创建 通讯录 的页面，从数据库获取信息并传入前端页面
def contact(request, id):
    # 获取该邮件信息
    contact = []
    tmp_contact = search_all("contacts")
    for var in tmp_contact:
        if var['owner_id'] == id:
            contact.append(var)

    template = loader.get_template('contact.html')

    context = {'contacts': contact,}
    return HttpResponse(template.render(context, request))

# 创建 草稿箱 的页面，从数据库获取信息并传入前端页面
def draft(request, id):
    # 获取该邮件信息
    draft = []
    tmp_draft = search_all("d_app")
    for var in tmp_draft:
        if var['sender_id'] == id:
            draft.append(var)

    template = loader.get_template('draft.html')

    context = {'draft': draft,}
    return HttpResponse(template.render(context, request))


# 创建 管理员管理 的页面，从数据库获取信息并传入前端页面
def adminpage(request):
    users=search_all('users')
    contacts=search_all('contacts')
    drafts=search_all('drafts')
    emails=search_all('emails')
    notebooks=search_all('notebooks')
    appendixs=search_all('appendixs')
    template=loader.get_template('adminpage.html')
    context={'users':users,
             'contacts':contacts,
             'drafts':drafts,
             'emails':emails,
             'notebooks':notebooks,
             'appendixs':appendixs,
            }
    
    return HttpResponse(template.render(context,request))

def faultpage(request):
    template=loader.get_template('faultpage.html')
    return HttpResponse(template.render({},request))